<?php

namespace App\Console\Commands;

use App\Episode;
use App\Show;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class CleanDb extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'zhou:clearDB';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '删除数据库中没有用的数据';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //对show的名字进行处理
        $this->deleteErrorShowItem();
        $this->deleteShowWithNoEpisode();
        //删除无用的episode
        $this->deleteTrashEpisodes();
        $this->deleDuplicateShowImageItemInDb();

    }

    protected function deleDuplicateShowImageItemInDb()
    {
        $array = DB::table('image_show')->get();
        $ids = [];
        foreach ($array as $vo) {
            $ids[$vo->show_id . '~~~~' . $vo->image_id] = $vo->id;
        }
        var_dump(count($ids));
        DB::table('image_show')->whereNotIn('id', array_values($ids))->delete();
    }

    protected function deleteTrashEpisodes()
    {
        $raw = 'LENGTH(url_torrent) < 4 and LENGTH(url_magnet) < 4 and LENGTH(url_ed2k) < 4 and LENGTH(url_baidupan) < 4 and LENGTH(url_video) < 4 and LENGTH(url_other) < 4';
        Episode::whereRaw($raw)->delete();
    }

    protected function deleteShowWithNoEpisode()
    {
        $deleShows = Show::has('episodes', '=', 0)->get();
        foreach ($deleShows as $show1) {
            $show1->images()->detach();

            $show1->delete();

            var_dump($show1->id);

        }
    }

    protected function deleteErrorShowItem()
    {
        Show::where('name_en', 'like', '%Torrents%')->get()->map(function ($item, $key) {
            $name_en = str_ireplace('Torrents', '', $item->name_en);
            $show = Show::firstOrCreate(['name_en' => $name_en]);
            $item->episodes->map(function ($item, $key) use ($show) {
                $item->show_id = $show->id;
            });
            $item->images()->detach();
            //var_dump($item->id);
            $item->delete();
        });
    }
}
